﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;
using System.Data;

namespace FillReportOnline
{
    public partial class adddata : System.Web.UI.Page
    {
        static string connectionStr = System.Configuration.ConfigurationManager.ConnectionStrings["datasource"].ToString();

        protected void Page_Load(object sender, EventArgs e)
        {
            if (IsPostBack)
            {
                return;
            }
            
            this.FpSpread1.Open(this.Server.MapPath(@"resource\template\adddata.xml"));
            //SetCellType();
            
            if (Session["skin"] != null)
            {
                int skin = (int)(Session["skin"]);
                FarPoint.Web.Spread.DefaultSkins.GetAt(skin).Apply(FpSpread1.Sheets[0]);
                this.FpSpread1.Sheets[0].GridLines = GridLines.None;
            }
            this.FpSpread1.CommandBar.Visible = true;

            //this.FpSpread1.Save(this.Server.MapPath("1.xml"), false);
            
            FarPoint.Web.Spread.CurrencyCellType cct = new FarPoint.Web.Spread.CurrencyCellType();
            cct.EditMode.NumberFormat = new System.Globalization.NumberFormatInfo();
            cct.EditMode.NumberFormat.CurrencySymbol = "¥";
            cct.NumberFormat = new System.Globalization.NumberFormatInfo();
            cct.NumberFormat.CurrencySymbol = "¥";
            cct.MinimumValue = 1000;
            cct.MaximumValue = 10000000;
            cct.NumberFormat.NumberDecimalDigits = 2;
            //cct.num
            cct.FormatString = "##,###,###.00,,";
            cct.NumberFormat.CurrencyGroupSeparator = ",";
            cct.ErrorMessage = "货币格式：例如，1234.56；数值范围：1000-10000000";
            FpSpread1.Sheets[0].Cells[6, 8].CellType = cct;
            FpSpread1.Sheets[0].Cells[6, 8].Value = 10000;

        }

        private void SetCellType()
        {
            FarPoint.Web.Spread.ButtonCellType bc = new FarPoint.Web.Spread.ButtonCellType();
            bc.Text = "提交订单";
            FpSpread1.ActiveSheetView.Cells[14, 8].CellType = bc;

            //数值单元格
            FarPoint.Web.Spread.Extender.NumericUpDownCellType n = new FarPoint.Web.Spread.Extender.NumericUpDownCellType();
            n.Maximum = 1000;
            n.Minimum = 1;
            n.Step = 1;
            n.Width = 130;
            n.ShowEditor = true;
            FpSpread1.ActiveSheetView.Cells[6, 4].CellType = n;


            //日历单元格
            FarPoint.Web.Spread.Extender.DateCalendarCellType dc = new FarPoint.Web.Spread.Extender.DateCalendarCellType();
            dc.Animated = true;
            dc.DateFormat = "MM/dd/yyyy";
            dc.EnableOnClient = true;
            dc.ShowEditor = true;
            FpSpread1.ActiveSheetView.Cells[4, 4].CellType = dc;
            
            //自动完成单元格
            FarPoint.Web.Spread.Extender.AutoCompleteCellType ac = new FarPoint.Web.Spread.Extender.AutoCompleteCellType();
            ac.CompletionInterval = 1;
            ac.CompletionSetCount = 5;
            ac.DelimiterCharacters = ";, :";
            AjaxControlToolkit.TextBoxWatermarkExtender twe = new AjaxControlToolkit.TextBoxWatermarkExtender();
            twe.WatermarkText = "输入'朱'测试...";
            ac.Extenders.Add(twe);
            ac.FirstRowSelected = true;
            ac.ServicePath = "WebService1.asmx";
            ac.ServiceMethod = "GetAllNames";
            ac.MinimumPrefixLength = 1;
            ac.EnableCaching = true;
            ac.ShowEditor = true;
            FpSpread1.ActiveSheetView.Cells[8, 8].CellType = ac;

            //输入备注
            FarPoint.Web.Spread.TextCellType textType = new FarPoint.Web.Spread.TextCellType();
            textType.Multiline = true;
            textType.ShowEditor = true;
            textType.AllowWrap = true;
            FpSpread1.ActiveSheetView.Cells[12, 2].BackColor = System.Drawing.Color.White;
            FpSpread1.ActiveSheetView.Cells[12, 2].CellType = textType;

        }

        protected void FpSpread1_ButtonCommand(object sender, FarPoint.Web.Spread.SpreadCommandEventArgs e)
        {
            if (e.CommandName != "Button")
            {
                return;
            }

            DateTime orderDate = (DateTime)(this.FpSpread1.Sheets[0].Cells[4, 4].Value);
            OleDbParameter date = new OleDbParameter("@date", OleDbType.Date);
            date.Value = orderDate.Date;

            string orderPro = this.FpSpread1.Sheets[0].Cells[4, 8].Text;
            OleDbParameter product = new OleDbParameter("@product", OleDbType.VarChar);
            product.Value = orderPro;

            int orderNO = Convert.ToInt32(this.FpSpread1.Sheets[0].Cells[6, 4].Value);
            OleDbParameter num = new OleDbParameter("@num", OleDbType.Integer);
            num.Value = orderNO;

            int orderSum = Convert.ToInt32(this.FpSpread1.Sheets[0].Cells[6, 8].Value);
            OleDbParameter sum = new OleDbParameter("@sum", OleDbType.Integer);
            sum.Value = orderSum;

            string orderRegion = this.FpSpread1.Sheets[0].Cells[8, 4].Text;
            OleDbParameter region = new OleDbParameter("@region", OleDbType.VarChar);
            region.Value = orderRegion;

            string orderManager = this.FpSpread1.Sheets[0].Cells[8, 8].Text;
            OleDbParameter manager = new OleDbParameter("@manager", OleDbType.VarChar);
            manager.Value = orderManager;

            string orderRemark = this.FpSpread1.Sheets[0].Cells[12, 2].Text;
            OleDbParameter remark = new OleDbParameter("@remark", OleDbType.VarChar);
            remark.Value = orderRemark;

            //string sqlcommand = "Insert into SalesData(合同号,日期,产品,数量,收入,区域,销售经理,备注) values(@合同号,'#@date#','@product','@num','@sum','@region','@manager','@remark')";
            string sqlcommand = "Insert into SalesData(日期,产品,数量,收入,区域,销售经理,备注) values(@date,@product,@num,@sum,@region,@manager,@remark)";
            //string sqlcommand = "Insert into SalesData(合同号,日期,产品,数量,收入,区域,销售经理,备注) values('@合同号','2012-12-14','@product','@num','@sum','@region','@manager','@remark')";

            OleDbParameter[] parameters = new OleDbParameter[] { date, product, num, sum, region, manager, remark };

            if (ExecuteSql(sqlcommand, parameters) != 0)
            {
                Response.Write("<script>if(confirm('添加数据成功，是否转到订单页查看？')){document.location.href='report.aspx';document.cookie = '@link@='+ 'report.aspx';}</script>");
            }
            else
            {
                Response.Write("<script>alert('请填写完整订单信息！')</script>");
            }
        }

        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string SQLString, params OleDbParameter[] cmdParms)
        {
            using (OleDbConnection connection = new OleDbConnection(connectionStr))
            {
                using (OleDbCommand cmd = new OleDbCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                        int rows = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        return rows;
                    }
                    catch (System.Data.OleDb.OleDbException E)
                    {
                        return 0;
                    }
                }
            }
        }

        private static void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, string cmdText, OleDbParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;//cmdType;
            if (cmdParms != null)
            {
                foreach (OleDbParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }
    }
}